﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections.ObjectModel;
using HellCook.Model;
using System.Data.SqlClient;
using System.IO;
using System.Xml.Serialization;

namespace HellCook.DataAccess
{
    public static class Receta_AdministradorBaseDeDatos
    {
        private static string PathDataBase = "Data Source=.\\SQLEXPRESS; Initial Catalog=HellCook_DataBase; Integrated Security=true";


        public static Collection<Receta> ObtenerTodasLasRecetas()
        {
            Collection<Receta> List_Recipes = null;
            SqlDataReader ReaderInfo = null;

            SqlConnection SQLConnection;
            SQLConnection = new SqlConnection(PathDataBase);

            SqlCommand SQLCommand = SQLConnection.CreateCommand();
            SQLCommand.CommandText = "SELECT * FROM [Recipe]";

            try
            {
                SQLConnection.Open();
                ReaderInfo = SQLCommand.ExecuteReader();

                List_Recipes = new Collection<Receta>();
                while (ReaderInfo.Read())
                {
                    Receta Item = new Receta();

                    Item.Codigo = Convert.ToInt16(ReaderInfo["Codigo"].ToString());
                    Item.Nombre_Receta = ReaderInfo["Nombre_Receta"].ToString();
                    Item.Descripcion = ReaderInfo["Descripcion"].ToString();
                    string IngredientsList = ReaderInfo["Lista_Ingredientes"].ToString();
                    Collection<NodoCantidadIngrediente> List = new Collection<NodoCantidadIngrediente>();
                    List = (Collection<NodoCantidadIngrediente>)Herramientas.XmlToObject(IngredientsList, List.GetType());

                    Item.Lista_Ingredientes = List;

                    List_Recipes.Add(Item);
                }
            }
            catch (Exception)
            {
                List_Recipes = null;
            }
            finally
            {
                if (SQLConnection.State == System.Data.ConnectionState.Open)
                {
                    SQLConnection.Close();
                }
            }

            return (List_Recipes);
        }

        public static Receta ObtenerUnaRecetaPorNombre(string nameRecipe)
        {
            Receta Item = null;

            if (!String.IsNullOrEmpty(nameRecipe))
            {
                SqlDataReader ReaderInfo = null;

                SqlConnection SQLConnection;
                SQLConnection = new SqlConnection(PathDataBase);

                SqlCommand SQLCommand = SQLConnection.CreateCommand();
                SQLCommand.CommandText = "SELECT * FROM [Recipe] WHERE Nombre_Receta=@Variable_Recipe_Name";
                SQLCommand.Parameters.AddWithValue("Variable_Recipe_Name", nameRecipe);

                try
                {
                    SQLConnection.Open();
                    ReaderInfo = SQLCommand.ExecuteReader();

                    while (ReaderInfo.Read())
                    {
                        Item = new Receta();

                        Item.Codigo = Convert.ToInt16(ReaderInfo["Codigo"].ToString());
                        Item.Nombre_Receta = ReaderInfo["Nombre_Receta"].ToString();
                        Item.Descripcion = ReaderInfo["Descripcion"].ToString();

                        string IngredientsList = ReaderInfo["Lista_Ingredientes"].ToString();
                        Collection<NodoCantidadIngrediente> List = new Collection<NodoCantidadIngrediente>();
                        List = (Collection<NodoCantidadIngrediente>)Herramientas.XmlToObject(IngredientsList, List.GetType());

                        Item.Lista_Ingredientes = List;
                    }
                }
                catch (Exception)
                {
                    Item = null;
                }
                finally
                {
                    if (SQLConnection.State == System.Data.ConnectionState.Open)
                    {
                        SQLConnection.Close();
                    }
                }
            }

            return (Item);
        }

        public static Collection<Receta> ObtenerRecetaSimilarA(string nameRecipe)
        {
            Collection<Receta> List_Recipes = null;
            SqlDataReader ReaderInfo = null;

            if (!String.IsNullOrEmpty(nameRecipe))
            {                
                SqlConnection SQLConnection;
                SQLConnection = new SqlConnection(PathDataBase);

                SqlCommand SQLCommand = SQLConnection.CreateCommand();
                SQLCommand.CommandText = "SELECT * FROM [Recipe] WHERE Nombre_Receta LIKE @Variable_Recipe_Name";
                SQLCommand.Parameters.AddWithValue("Variable_Recipe_Name", nameRecipe + "%");

                try
                {
                    SQLConnection.Open();
                    ReaderInfo = SQLCommand.ExecuteReader();

                    List_Recipes = new Collection<Receta>();

                    while (ReaderInfo.Read())
                    {
                        Receta Item = new Receta();

                        Item.Codigo = Convert.ToInt16(ReaderInfo["Codigo"].ToString());
                        Item.Nombre_Receta = ReaderInfo["Nombre_Receta"].ToString();
                        Item.Descripcion = ReaderInfo["Descripcion"].ToString();

                        string IngredientsList = ReaderInfo["Lista_Ingredientes"].ToString();
                        Collection<NodoCantidadIngrediente> List = new Collection<NodoCantidadIngrediente>();
                        List = (Collection<NodoCantidadIngrediente>)Herramientas.XmlToObject(IngredientsList, List.GetType());
                        
                        Item.Lista_Ingredientes = List;

                        List_Recipes.Add(Item);
                    }
                }
                catch (Exception)
                {
                    List_Recipes = null;
                }
                finally
                {
                    if (SQLConnection.State == System.Data.ConnectionState.Open)
                    {
                        SQLConnection.Close();
                    }
                }
            }

            return (List_Recipes);
        }

        public static Receta ObtenerUnaRecetaPorCodigo(string code)
        {
            Receta Item = null;

            if (!String.IsNullOrEmpty(code))
            {
                SqlDataReader ReaderInfo = null;

                SqlConnection SQLConnection;
                SQLConnection = new SqlConnection(PathDataBase);

                SqlCommand SQLCommand = SQLConnection.CreateCommand();
                SQLCommand.CommandText = "SELECT * FROM [Recipe] WHERE Codigo=@Variable_Code";
                SQLCommand.Parameters.AddWithValue("Variable_Code", code);

                try
                {
                    SQLConnection.Open();
                    ReaderInfo = SQLCommand.ExecuteReader();

                    while (ReaderInfo.Read())
                    {
                        Item = new Receta();

                        Item.Codigo = Convert.ToInt16(ReaderInfo["Codigo"].ToString());
                        Item.Nombre_Receta = ReaderInfo["Nombre_Receta"].ToString();
                        Item.Descripcion = ReaderInfo["Descripcion"].ToString();

                        string IngredientsList = ReaderInfo["Lista_Ingredientes"].ToString();
                        Collection<NodoCantidadIngrediente> List = new Collection<NodoCantidadIngrediente>();
                        List = (Collection<NodoCantidadIngrediente>)Herramientas.XmlToObject(IngredientsList, List.GetType());

                        Item.Lista_Ingredientes = List;
                    }
                }
                catch (Exception)
                {
                    Item = null;
                }
                finally
                {
                    if (SQLConnection.State == System.Data.ConnectionState.Open)
                    {
                        SQLConnection.Close();
                    }
                }
            }

            return (Item);
        }

        public static string InsertarReceta(Receta recipe)
        {
            string Message = "Se agrego correctamente.";

            SqlConnection SQLConnection;
            SQLConnection = new SqlConnection(PathDataBase);

            SqlCommand SQLCommand = SQLConnection.CreateCommand();
            SQLCommand.CommandText = "INSERT INTO [Recipe] (Nombre_Receta, Lista_Ingredientes, Descripcion) VALUES(@Variable_Recipe_Name, @Variable_List_Ingredient, @Variable_Description)";

            SQLCommand.Parameters.AddWithValue("Variable_Recipe_Name", recipe.Nombre_Receta);                                 
            SQLCommand.Parameters.AddWithValue("Variable_List_Ingredient", HellCook.DataAccess.Herramientas.ObjectToXml(recipe.Lista_Ingredientes));
            SQLCommand.Parameters.AddWithValue("Variable_Description", recipe.Descripcion);            

            try
            {
                SQLConnection.Open();

                int Result = SQLCommand.ExecuteNonQuery();

                if (Result <= 0)
                {
                    Message = "Error al insertar";
                }
            }
            catch (Exception exception)
            {
                Message = exception.Message;
            }
            finally
            {
                if (SQLConnection.State == System.Data.ConnectionState.Open)
                {
                    SQLConnection.Close();
                }
            }

            return (Message);
        }
        
        ////////////////////

        public static string ModifcarReceta(Receta recipe)
        {
            string Message = "Se modifico correctamente.";

            SqlConnection SQLConnection;
            SQLConnection = new SqlConnection(PathDataBase);

            SqlCommand SQLCommand = SQLConnection.CreateCommand();
            //SQLCommand.CommandText = "INSERT INTO [Recipe] (Nombre_Receta, Lista_Ingredientes, Descripcion) VALUES(@Variable_Recipe_Name, @Variable_List_Ingredient, @Variable_Description)";
            SQLCommand.CommandText = "UPDATE [Recipe] SET Nombre_Receta = @Variable_Recipe_Name , Lista_Ingredientes = @Variable_List_Ingredient , Descripcion = @Variable_Description WHERE Codigo = @Variable_Codigo";

            SQLCommand.Parameters.AddWithValue("Variable_Recipe_Name", recipe.Nombre_Receta);
            SQLCommand.Parameters.AddWithValue("Variable_Codigo", recipe.Codigo);
            SQLCommand.Parameters.AddWithValue("Variable_List_Ingredient", HellCook.DataAccess.Herramientas.ObjectToXml(recipe.Lista_Ingredientes));
            SQLCommand.Parameters.AddWithValue("Variable_Description", recipe.Descripcion);

            try
            {
                SQLConnection.Open();

                int Result = SQLCommand.ExecuteNonQuery();

                if (Result <= 0)
                {
                    Message = "Error al modificar";
                }
            }
            catch (Exception exception)
            {
                Message = exception.Message;
            }
            finally
            {
                if (SQLConnection.State == System.Data.ConnectionState.Open)
                {
                    SQLConnection.Close();
                }
            }

            return (Message);
        }

    }
}
